set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Advertise database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Advertise_CreateNewAdvertise]
	@Title NVarChar(128),
	@ImageFolder NVarChar(128),
	@CompanyName NVarChar(128)

AS

INSERT INTO [dbo].[tbl_advertise]
(
	title,
	image_folder,
	company_name

)
VALUES
(
	@Title,
	@ImageFolder,
	@CompanyName

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Advertise database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Advertise_DeleteAdvertise]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_advertise]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Advertise from the Advertise 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Advertise_DeleteAdvertiseList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_advertise]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Advertise table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Advertise_GetAdvertiseById]
@Id Int
AS
SELECT
	id, 
	title,
	image_folder,
	company_name

FROM [dbo].[tbl_advertise]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Advertise database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Advertise_GetAllAdvertise]
AS
SELECT
	id, 
	title,
	image_folder,
	company_name

FROM [dbo].[tbl_advertise]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Advertise_GetAllAdvertisePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_advertise]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	title,
	image_folder,
	company_name

FROM [dbo].[tbl_advertise]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Advertise_GetAllAdvertisePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_advertise]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Advertise database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Advertise_UpdateAdvertise]
@Id int,
	@Title NVarChar(128),
	@ImageFolder NVarChar(128),
	@CompanyName NVarChar(128)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_advertise]
SET

	title = @Title,
	image_folder = @ImageFolder,
	company_name = @CompanyName
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Area database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Area_CreateNewArea]
	@AreaName NVarChar(64),
	@CityId Int

AS

INSERT INTO [dbo].[tbl_area]
(
	area_name,
	city_id

)
VALUES
(
	@AreaName,
	@CityId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Area database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Area_DeleteArea]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_area]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Area from the Area 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Area_DeleteAreaList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_area]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Area table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Area_GetAreaById]
@Id Int
AS
SELECT
	id, 
	area_name,
	city_id

FROM [dbo].[tbl_area]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Area database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Area_GetAllArea]
AS
SELECT
	id, 
	area_name,
	city_id

FROM [dbo].[tbl_area]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Area_GetAllAreaPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_area]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	area_name,
	city_id

FROM [dbo].[tbl_area]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Area_GetAllAreaPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_area]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Area database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Area_UpdateArea]
@Id int,
	@AreaName NVarChar(64),
	@CityId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_area]
SET

	area_name = @AreaName,
	city_id = @CityId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Area_GetAreaByCityId]
@CityId Int
AS
SELECT 	id,
	area_name,
	city_id

FROM [dbo].[tbl_area]
WHERE city_id=@CityId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Area_GetAreaByCityIdPaged]
(
@CityId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_area]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	area_name,
	city_id

FROM [dbo].[tbl_area]
WHERE city_id = ' +  CONVERT(nvarchar(10),@CityId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Area_GetAreaByCityIdPagedCount]
@CityId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_area]
	WHERE city_id = @CityId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Article database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Article_CreateNewArticle]
	@ArticleTitle NVarChar(512),
	@ArticleText NText,
	@Rating Decimal,
	@TotalRater Int,
	@TotalView Int,
	@ImagePath NVarChar(128),
	@RevieweId Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@Keywords NVarChar(512)

AS

INSERT INTO [dbo].[tbl_article]
(
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

)
VALUES
(
	@ArticleTitle,
	@ArticleText,
	@Rating,
	@TotalRater,
	@TotalView,
	@ImagePath,
	@RevieweId,
	@CreatedOn,
	@LastUpdate,
	@Keywords

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Article database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Article_DeleteArticle]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_article]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Article from the Article 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Article_DeleteArticleList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_article]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Article table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Article_GetArticleById]
@Id Int
AS
SELECT
	id, 
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

FROM [dbo].[tbl_article]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Article database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Article_GetAllArticle]
AS
SELECT
	id, 
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

FROM [dbo].[tbl_article]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Article_GetAllArticlePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_article]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

FROM [dbo].[tbl_article]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Article_GetAllArticlePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_article]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Article database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Article_UpdateArticle]
@Id int,
	@ArticleTitle NVarChar(512),
	@ArticleText NText,
	@Rating Decimal,
	@TotalRater Int,
	@TotalView Int,
	@ImagePath NVarChar(128),
	@RevieweId Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@Keywords NVarChar(512)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_article]
SET

	article_title = @ArticleTitle,
	article_text = @ArticleText,
	rating = @Rating,
	total_rater = @TotalRater,
	total_view = @TotalView,
	image_path = @ImagePath,
	reviewe_id = @RevieweId,
	created_on = @CreatedOn,
	last_update = @LastUpdate,
	keywords = @Keywords
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Article_GetArticleByRevieweId]
@RevieweId Int
AS
SELECT 	id,
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

FROM [dbo].[tbl_article]
WHERE reviewe_id=@RevieweId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Article_GetArticleByRevieweIdPaged]
(
@RevieweId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_article]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	article_title,
	article_text,
	rating,
	total_rater,
	total_view,
	image_path,
	reviewe_id,
	created_on,
	last_update,
	keywords

FROM [dbo].[tbl_article]
WHERE reviewe_id = ' +  CONVERT(nvarchar(10),@RevieweId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Article_GetArticleByRevieweIdPagedCount]
@RevieweId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_article]
	WHERE reviewe_id = @RevieweId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Category database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Category_CreateNewCategory]
	@CategoryName NVarChar(64),
	@ParentCategoryId Int,
	@ItemCount Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@TotalView Int

AS

INSERT INTO [dbo].[tbl_category]
(
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

)
VALUES
(
	@CategoryName,
	@ParentCategoryId,
	@ItemCount,
	@CreatedOn,
	@LastUpdate,
	@TotalView

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Category database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Category_DeleteCategory]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_category]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Category from the Category 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Category_DeleteCategoryList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_category]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Category table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Category_GetCategoryById]
@Id Int
AS
SELECT
	id, 
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_category]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Category database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Category_GetAllCategory]
AS
SELECT
	id, 
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_category]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Category_GetAllCategoryPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_category]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_category]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Category_GetAllCategoryPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_category]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Category database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Category_UpdateCategory]
@Id int,
	@CategoryName NVarChar(64),
	@ParentCategoryId Int,
	@ItemCount Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@TotalView Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_category]
SET

	category_name = @CategoryName,
	parent_category_id = @ParentCategoryId,
	item_count = @ItemCount,
	created_on = @CreatedOn,
	last_update = @LastUpdate,
	total_view = @TotalView
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Category_GetCategoryByParentCategoryId]
@ParentCategoryId Int
AS
SELECT 	id,
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_category]
WHERE parent_category_id=@ParentCategoryId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Category_GetCategoryByParentCategoryIdPaged]
(
@ParentCategoryId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_category]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	category_name,
	parent_category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_category]
WHERE parent_category_id = ' +  CONVERT(nvarchar(10),@ParentCategoryId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Category_GetCategoryByParentCategoryIdPagedCount]
@ParentCategoryId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_category]
	WHERE parent_category_id = @ParentCategoryId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the City database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_City_CreateNewCity]
	@CityName NVarChar(64),
	@CountryId Int

AS

INSERT INTO [dbo].[tbl_city]
(
	city_name,
	country_id

)
VALUES
(
	@CityName,
	@CountryId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the City database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_City_DeleteCity]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_city]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of City from the City 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_City_DeleteCityList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_city]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of City table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_City_GetCityById]
@Id Int
AS
SELECT
	id, 
	city_name,
	country_id

FROM [dbo].[tbl_city]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the City database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_City_GetAllCity]
AS
SELECT
	id, 
	city_name,
	country_id

FROM [dbo].[tbl_city]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_City_GetAllCityPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_city]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	city_name,
	country_id

FROM [dbo].[tbl_city]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_City_GetAllCityPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_city]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the City database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_City_UpdateCity]
@Id int,
	@CityName NVarChar(64),
	@CountryId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_city]
SET

	city_name = @CityName,
	country_id = @CountryId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_City_GetCityByCountryId]
@CountryId Int
AS
SELECT 	id,
	city_name,
	country_id

FROM [dbo].[tbl_city]
WHERE country_id=@CountryId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_City_GetCityByCountryIdPaged]
(
@CountryId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_city]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	city_name,
	country_id

FROM [dbo].[tbl_city]
WHERE country_id = ' +  CONVERT(nvarchar(10),@CountryId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_City_GetCityByCountryIdPagedCount]
@CountryId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_city]
	WHERE country_id = @CountryId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Content database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Content_CreateNewContent]
	@ContentText NText,
	@LastUpdate DateTime

AS

INSERT INTO [dbo].[tbl_content]
(
	content_text,
	last_update

)
VALUES
(
	@ContentText,
	@LastUpdate

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Content database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Content_DeleteContent]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_content]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Content from the Content 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Content_DeleteContentList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_content]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Content table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Content_GetContentById]
@Id Int
AS
SELECT
	id, 
	content_text,
	last_update

FROM [dbo].[tbl_content]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Content database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Content_GetAllContent]
AS
SELECT
	id, 
	content_text,
	last_update

FROM [dbo].[tbl_content]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Content_GetAllContentPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_content]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	content_text,
	last_update

FROM [dbo].[tbl_content]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Content_GetAllContentPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_content]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Content database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Content_UpdateContent]
@Id int,
	@ContentText NText,
	@LastUpdate DateTime

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_content]
SET

	content_text = @ContentText,
	last_update = @LastUpdate
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Country database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Country_CreateNewCountry]
	@CountryName NVarChar(64)

AS

INSERT INTO [dbo].[tbl_country]
(
	country_name

)
VALUES
(
	@CountryName

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Country database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Country_DeleteCountry]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_country]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Country from the Country 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Country_DeleteCountryList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_country]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Country table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Country_GetCountryById]
@Id Int
AS
SELECT
	id, 
	country_name

FROM [dbo].[tbl_country]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Country database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Country_GetAllCountry]
AS
SELECT
	id, 
	country_name

FROM [dbo].[tbl_country]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Country_GetAllCountryPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_country]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	country_name

FROM [dbo].[tbl_country]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Country_GetAllCountryPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_country]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Country database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Country_UpdateCountry]
@Id int,
	@CountryName NVarChar(64)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_country]
SET

	country_name = @CountryName
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Favorites database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Favorites_CreateNewFavorites]
	@ProductId Int,
	@WhenAdded DateTime,
	@ForUserId Int

AS

INSERT INTO [dbo].[tbl_favorites]
(
	product_id,
	when_added,
	for_user_id

)
VALUES
(
	@ProductId,
	@WhenAdded,
	@ForUserId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Favorites database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Favorites_DeleteFavorites]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_favorites]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Favorites from the Favorites 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Favorites_DeleteFavoritesList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_favorites]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Favorites table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesById]
@Id Int
AS
SELECT
	id, 
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Favorites database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Favorites_GetAllFavorites]
AS
SELECT
	id, 
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Favorites_GetAllFavoritesPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_favorites]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Favorites_GetAllFavoritesPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_favorites]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Favorites database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Favorites_UpdateFavorites]
@Id int,
	@ProductId Int,
	@WhenAdded DateTime,
	@ForUserId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_favorites]
SET

	product_id = @ProductId,
	when_added = @WhenAdded,
	for_user_id = @ForUserId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByProductId]
@ProductId Int
AS
SELECT 	id,
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE product_id=@ProductId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByProductIdPaged]
(
@ProductId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_favorites]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE product_id = ' +  CONVERT(nvarchar(10),@ProductId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByProductIdPagedCount]
@ProductId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_favorites]
	WHERE product_id = @ProductId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByForUserId]
@ForUserId Int
AS
SELECT 	id,
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE for_user_id=@ForUserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByForUserIdPaged]
(
@ForUserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_favorites]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_id,
	when_added,
	for_user_id

FROM [dbo].[tbl_favorites]
WHERE for_user_id = ' +  CONVERT(nvarchar(10),@ForUserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Favorites_GetFavoritesByForUserIdPagedCount]
@ForUserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_favorites]
	WHERE for_user_id = @ForUserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Membership database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Membership_CreateNewMembership]
	@PackageName NVarChar(64),
	@ExpireDate DateTime,
	@PackageCost Decimal

AS

INSERT INTO [dbo].[tbl_membership]
(
	package_name,
	expire_date,
	package_cost

)
VALUES
(
	@PackageName,
	@ExpireDate,
	@PackageCost

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Membership database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Membership_DeleteMembership]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_membership]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Membership from the Membership 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Membership_DeleteMembershipList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_membership]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Membership table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Membership_GetMembershipById]
@Id Int
AS
SELECT
	id, 
	package_name,
	expire_date,
	package_cost

FROM [dbo].[tbl_membership]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Membership database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Membership_GetAllMembership]
AS
SELECT
	id, 
	package_name,
	expire_date,
	package_cost

FROM [dbo].[tbl_membership]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Membership_GetAllMembershipPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_membership]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	package_name,
	expire_date,
	package_cost

FROM [dbo].[tbl_membership]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Membership_GetAllMembershipPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_membership]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Membership database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Membership_UpdateMembership]
@Id int,
	@PackageName NVarChar(64),
	@ExpireDate DateTime,
	@PackageCost Decimal

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_membership]
SET

	package_name = @PackageName,
	expire_date = @ExpireDate,
	package_cost = @PackageCost
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the MembershipExpire database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_MembershipExpire_CreateNewMembershipExpire]
	@UserId Int,
	@MembershipId Int,
	@ExpireDate DateTime,
	@Discount Decimal

AS

INSERT INTO [dbo].[tbl_membershipExpire]
(
	user_id,
	membership_id,
	expire_date,
	discount

)
VALUES
(
	@UserId,
	@MembershipId,
	@ExpireDate,
	@Discount

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the MembershipExpire database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_MembershipExpire_DeleteMembershipExpire]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_membershipExpire]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of MembershipExpire from the MembershipExpire 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_MembershipExpire_DeleteMembershipExpireList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_membershipExpire]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of MembershipExpire table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireById]
@Id Int
AS
SELECT
	id, 
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the MembershipExpire database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetAllMembershipExpire]
AS
SELECT
	id, 
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetAllMembershipExpirePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_membershipExpire]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetAllMembershipExpirePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_membershipExpire]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the MembershipExpire database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_MembershipExpire_UpdateMembershipExpire]
@Id int,
	@UserId Int,
	@MembershipId Int,
	@ExpireDate DateTime,
	@Discount Decimal

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_membershipExpire]
SET

	user_id = @UserId,
	membership_id = @MembershipId,
	expire_date = @ExpireDate,
	discount = @Discount
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByUserId]
@UserId Int
AS
SELECT 	id,
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE user_id=@UserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByUserIdPaged]
(
@UserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_membershipExpire]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE user_id = ' +  CONVERT(nvarchar(10),@UserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByUserIdPagedCount]
@UserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_membershipExpire]
	WHERE user_id = @UserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByMembershipId]
@MembershipId Int
AS
SELECT 	id,
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE membership_id=@MembershipId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByMembershipIdPaged]
(
@MembershipId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_membershipExpire]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_id,
	membership_id,
	expire_date,
	discount

FROM [dbo].[tbl_membershipExpire]
WHERE membership_id = ' +  CONVERT(nvarchar(10),@MembershipId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_MembershipExpire_GetMembershipExpireByMembershipIdPagedCount]
@MembershipId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_membershipExpire]
	WHERE membership_id = @MembershipId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Message database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Message_CreateNewMessage]
	@SenderUserId Int,
	@ReceiverUserId Int,
	@MessageBody NText,
	@WhenSent DateTime,
	@IsRead Bit,
	@ParentMessageId Int

AS

INSERT INTO [dbo].[tbl_message]
(
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

)
VALUES
(
	@SenderUserId,
	@ReceiverUserId,
	@MessageBody,
	@WhenSent,
	@IsRead,
	@ParentMessageId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Message database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Message_DeleteMessage]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_message]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Message from the Message 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Message_DeleteMessageList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_message]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Message table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Message_GetMessageById]
@Id Int
AS
SELECT
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Message database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Message_GetAllMessage]
AS
SELECT
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Message_GetAllMessagePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_message]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Message_GetAllMessagePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_message]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Message database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Message_UpdateMessage]
@Id int,
	@SenderUserId Int,
	@ReceiverUserId Int,
	@MessageBody NText,
	@WhenSent DateTime,
	@IsRead Bit,
	@ParentMessageId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_message]
SET

	sender_user_id = @SenderUserId,
	receiver_user_id = @ReceiverUserId,
	message_body = @MessageBody,
	when_sent = @WhenSent,
	is_read = @IsRead,
	parent_message_id = @ParentMessageId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Message_GetMessageBySenderUserId]
@SenderUserId Int
AS
SELECT 	id,
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE sender_user_id=@SenderUserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Message_GetMessageBySenderUserIdPaged]
(
@SenderUserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_message]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE sender_user_id = ' +  CONVERT(nvarchar(10),@SenderUserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Message_GetMessageBySenderUserIdPagedCount]
@SenderUserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_message]
	WHERE sender_user_id = @SenderUserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Message_GetMessageByReceiverUserId]
@ReceiverUserId Int
AS
SELECT 	id,
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE receiver_user_id=@ReceiverUserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Message_GetMessageByReceiverUserIdPaged]
(
@ReceiverUserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_message]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE receiver_user_id = ' +  CONVERT(nvarchar(10),@ReceiverUserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Message_GetMessageByReceiverUserIdPagedCount]
@ReceiverUserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_message]
	WHERE receiver_user_id = @ReceiverUserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Message_GetMessageByParentMessageId]
@ParentMessageId Int
AS
SELECT 	id,
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE parent_message_id=@ParentMessageId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Message_GetMessageByParentMessageIdPaged]
(
@ParentMessageId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_message]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	sender_user_id,
	receiver_user_id,
	message_body,
	when_sent,
	is_read,
	parent_message_id

FROM [dbo].[tbl_message]
WHERE parent_message_id = ' +  CONVERT(nvarchar(10),@ParentMessageId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Message_GetMessageByParentMessageIdPagedCount]
@ParentMessageId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_message]
	WHERE parent_message_id = @ParentMessageId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Product database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Product_CreateNewProduct]
	@ProductName NVarChar(64),
	@ProductDescription NText,
	@ImagePath NVarChar(128),
	@ForMale Bit,
	@CategoryId Int,
	@IsFeatured Bit,
	@TotalView Int,
	@ShopId Int,
	@ProductCode NVarChar(16),
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@PriceLow Decimal,
	@PriceHigh Decimal,
	@StockAmount Int,
	@AvailableFrom DateTime,
	@SpecialOffer NText,
	@Rating Decimal,
	@TotalRater Int,
	@Priority Int,
	@Keywords NVarChar(512)

AS

INSERT INTO [dbo].[tbl_product]
(
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

)
VALUES
(
	@ProductName,
	@ProductDescription,
	@ImagePath,
	@ForMale,
	@CategoryId,
	@IsFeatured,
	@TotalView,
	@ShopId,
	@ProductCode,
	@CreatedOn,
	@LastUpdate,
	@PriceLow,
	@PriceHigh,
	@StockAmount,
	@AvailableFrom,
	@SpecialOffer,
	@Rating,
	@TotalRater,
	@Priority,
	@Keywords

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Product database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Product_DeleteProduct]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_product]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Product from the Product 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Product_DeleteProductList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_product]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Product table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Product_GetProductById]
@Id Int
AS
SELECT
	id, 
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Product database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Product_GetAllProduct]
AS
SELECT
	id, 
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Product_GetAllProductPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_product]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Product_GetAllProductPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_product]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Product database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Product_UpdateProduct]
@Id int,
	@ProductName NVarChar(64),
	@ProductDescription NText,
	@ImagePath NVarChar(128),
	@ForMale Bit,
	@CategoryId Int,
	@IsFeatured Bit,
	@TotalView Int,
	@ShopId Int,
	@ProductCode NVarChar(16),
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@PriceLow Decimal,
	@PriceHigh Decimal,
	@StockAmount Int,
	@AvailableFrom DateTime,
	@SpecialOffer NText,
	@Rating Decimal,
	@TotalRater Int,
	@Priority Int,
	@Keywords NVarChar(512)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_product]
SET

	product_name = @ProductName,
	product_description = @ProductDescription,
	image_path = @ImagePath,
	for_male = @ForMale,
	category_id = @CategoryId,
	is_featured = @IsFeatured,
	total_view = @TotalView,
	shop_id = @ShopId,
	product_code = @ProductCode,
	created_on = @CreatedOn,
	last_update = @LastUpdate,
	price_low = @PriceLow,
	price_high = @PriceHigh,
	stock_amount = @StockAmount,
	available_from = @AvailableFrom,
	special_offer = @SpecialOffer,
	rating = @Rating,
	total_rater = @TotalRater,
	priority = @Priority,
	keywords = @Keywords
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Product_GetProductByCategoryId]
@CategoryId Int
AS
SELECT 	id,
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE category_id=@CategoryId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Product_GetProductByCategoryIdPaged]
(
@CategoryId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_product]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE category_id = ' +  CONVERT(nvarchar(10),@CategoryId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Product_GetProductByCategoryIdPagedCount]
@CategoryId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_product]
	WHERE category_id = @CategoryId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Product_GetProductByShopId]
@ShopId Int
AS
SELECT 	id,
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE shop_id=@ShopId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Product_GetProductByShopIdPaged]
(
@ShopId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_product]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	product_name,
	product_description,
	image_path,
	for_male,
	category_id,
	is_featured,
	total_view,
	shop_id,
	product_code,
	created_on,
	last_update,
	price_low,
	price_high,
	stock_amount,
	available_from,
	special_offer,
	rating,
	total_rater,
	priority,
	keywords

FROM [dbo].[tbl_product]
WHERE shop_id = ' +  CONVERT(nvarchar(10),@ShopId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Product_GetProductByShopIdPagedCount]
@ShopId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_product]
	WHERE shop_id = @ShopId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Profile database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Profile_CreateNewProfile]
	@UserFirstName NVarChar(32),
	@UserLastName NVarChar(32),
	@AreaId Int,
	@TimeZone Int,
	@SelectedTheme NVarChar(16),
	@UserId Int

AS

INSERT INTO [dbo].[tbl_profile]
(
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

)
VALUES
(
	@UserFirstName,
	@UserLastName,
	@AreaId,
	@TimeZone,
	@SelectedTheme,
	@UserId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Profile database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Profile_DeleteProfile]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_profile]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Profile from the Profile 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Profile_DeleteProfileList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_profile]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Profile table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Profile_GetProfileById]
@Id Int
AS
SELECT
	id, 
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Profile database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Profile_GetAllProfile]
AS
SELECT
	id, 
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Profile_GetAllProfilePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_profile]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Profile_GetAllProfilePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_profile]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Profile database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Profile_UpdateProfile]
@Id int,
	@UserFirstName NVarChar(32),
	@UserLastName NVarChar(32),
	@AreaId Int,
	@TimeZone Int,
	@SelectedTheme NVarChar(16),
	@UserId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_profile]
SET

	user_first_name = @UserFirstName,
	user_last_name = @UserLastName,
	area_id = @AreaId,
	time_zone = @TimeZone,
	selected_theme = @SelectedTheme,
	user_id = @UserId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByAreaId]
@AreaId Int
AS
SELECT 	id,
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE area_id=@AreaId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByAreaIdPaged]
(
@AreaId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_profile]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE area_id = ' +  CONVERT(nvarchar(10),@AreaId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByAreaIdPagedCount]
@AreaId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_profile]
	WHERE area_id = @AreaId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByUserId]
@UserId Int
AS
SELECT 	id,
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE user_id=@UserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByUserIdPaged]
(
@UserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_profile]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_first_name,
	user_last_name,
	area_id,
	time_zone,
	selected_theme,
	user_id

FROM [dbo].[tbl_profile]
WHERE user_id = ' +  CONVERT(nvarchar(10),@UserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Profile_GetProfileByUserIdPagedCount]
@UserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_profile]
	WHERE user_id = @UserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Reviewer database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Reviewer_CreateNewReviewer]
	@ReviewerName NVarChar(64),
	@ShortDescription NText,
	@Rating Decimal,
	@TotalRater Int,
	@ImagePath NVarChar(128)

AS

INSERT INTO [dbo].[tbl_reviewer]
(
	reviewer_name,
	short_description,
	rating,
	total_rater,
	image_path

)
VALUES
(
	@ReviewerName,
	@ShortDescription,
	@Rating,
	@TotalRater,
	@ImagePath

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Reviewer database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Reviewer_DeleteReviewer]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_reviewer]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Reviewer from the Reviewer 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Reviewer_DeleteReviewerList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_reviewer]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Reviewer table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Reviewer_GetReviewerById]
@Id Int
AS
SELECT
	id, 
	reviewer_name,
	short_description,
	rating,
	total_rater,
	image_path

FROM [dbo].[tbl_reviewer]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Reviewer database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Reviewer_GetAllReviewer]
AS
SELECT
	id, 
	reviewer_name,
	short_description,
	rating,
	total_rater,
	image_path

FROM [dbo].[tbl_reviewer]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Reviewer_GetAllReviewerPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_reviewer]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	reviewer_name,
	short_description,
	rating,
	total_rater,
	image_path

FROM [dbo].[tbl_reviewer]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Reviewer_GetAllReviewerPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_reviewer]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Reviewer database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Reviewer_UpdateReviewer]
@Id int,
	@ReviewerName NVarChar(64),
	@ShortDescription NText,
	@Rating Decimal,
	@TotalRater Int,
	@ImagePath NVarChar(128)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_reviewer]
SET

	reviewer_name = @ReviewerName,
	short_description = @ShortDescription,
	rating = @Rating,
	total_rater = @TotalRater,
	image_path = @ImagePath
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Shop database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Shop_CreateNewShop]
	@ShopName NVarChar(64),
	@ShopLogoPath NVarChar(128),
	@ShopOwnerUserId Int,
	@Rating Decimal,
	@TotalRater Int,
	@BranchAddress NVarChar(512),
	@Email NVarChar(64),
	@Phone NVarChar(32),
	@ShopCode NVarChar(16),
	@ItemCount Int,
	@CategoryCount Int,
	@TotalVisit Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@MainBranchShopId Int,
	@AreaId Int,
	@IsFeatured Bit,
	@Keywords NVarChar(512)

AS

INSERT INTO [dbo].[tbl_shop]
(
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

)
VALUES
(
	@ShopName,
	@ShopLogoPath,
	@ShopOwnerUserId,
	@Rating,
	@TotalRater,
	@BranchAddress,
	@Email,
	@Phone,
	@ShopCode,
	@ItemCount,
	@CategoryCount,
	@TotalVisit,
	@CreatedOn,
	@LastUpdate,
	@MainBranchShopId,
	@AreaId,
	@IsFeatured,
	@Keywords

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Shop database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Shop_DeleteShop]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_shop]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Shop from the Shop 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Shop_DeleteShopList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_shop]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Shop table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Shop_GetShopById]
@Id Int
AS
SELECT
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Shop database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Shop_GetAllShop]
AS
SELECT
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Shop_GetAllShopPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shop]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Shop_GetAllShopPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shop]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Shop database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Shop_UpdateShop]
@Id int,
	@ShopName NVarChar(64),
	@ShopLogoPath NVarChar(128),
	@ShopOwnerUserId Int,
	@Rating Decimal,
	@TotalRater Int,
	@BranchAddress NVarChar(512),
	@Email NVarChar(64),
	@Phone NVarChar(32),
	@ShopCode NVarChar(16),
	@ItemCount Int,
	@CategoryCount Int,
	@TotalVisit Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@MainBranchShopId Int,
	@AreaId Int,
	@IsFeatured Bit,
	@Keywords NVarChar(512)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_shop]
SET

	shop_name = @ShopName,
	shop_logo_path = @ShopLogoPath,
	shop_owner_user_id = @ShopOwnerUserId,
	rating = @Rating,
	total_rater = @TotalRater,
	branch_address = @BranchAddress,
	email = @Email,
	phone = @Phone,
	shop_code = @ShopCode,
	item_count = @ItemCount,
	category_count = @CategoryCount,
	total_visit = @TotalVisit,
	created_on = @CreatedOn,
	last_update = @LastUpdate,
	main_branch_shop_id = @MainBranchShopId,
	area_id = @AreaId,
	is_featured = @IsFeatured,
	keywords = @Keywords
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByShopOwnerUserId]
@ShopOwnerUserId Int
AS
SELECT 	id,
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE shop_owner_user_id=@ShopOwnerUserId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByShopOwnerUserIdPaged]
(
@ShopOwnerUserId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shop]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE shop_owner_user_id = ' +  CONVERT(nvarchar(10),@ShopOwnerUserId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Shop_GetShopByShopOwnerUserIdPagedCount]
@ShopOwnerUserId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shop]
	WHERE shop_owner_user_id = @ShopOwnerUserId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByMainBranchShopId]
@MainBranchShopId Int
AS
SELECT 	id,
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE main_branch_shop_id=@MainBranchShopId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByMainBranchShopIdPaged]
(
@MainBranchShopId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shop]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE main_branch_shop_id = ' +  CONVERT(nvarchar(10),@MainBranchShopId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Shop_GetShopByMainBranchShopIdPagedCount]
@MainBranchShopId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shop]
	WHERE main_branch_shop_id = @MainBranchShopId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByAreaId]
@AreaId Int
AS
SELECT 	id,
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE area_id=@AreaId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Shop_GetShopByAreaIdPaged]
(
@AreaId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shop]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_name,
	shop_logo_path,
	shop_owner_user_id,
	rating,
	total_rater,
	branch_address,
	email,
	phone,
	shop_code,
	item_count,
	category_count,
	total_visit,
	created_on,
	last_update,
	main_branch_shop_id,
	area_id,
	is_featured,
	keywords

FROM [dbo].[tbl_shop]
WHERE area_id = ' +  CONVERT(nvarchar(10),@AreaId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_Shop_GetShopByAreaIdPagedCount]
@AreaId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shop]
	WHERE area_id = @AreaId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the ShopCategory database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_ShopCategory_CreateNewShopCategory]
	@ShopId Int,
	@CategoryId Int,
	@ItemCount Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@TotalView Int

AS

INSERT INTO [dbo].[tbl_shopCategory]
(
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

)
VALUES
(
	@ShopId,
	@CategoryId,
	@ItemCount,
	@CreatedOn,
	@LastUpdate,
	@TotalView

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the ShopCategory database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_ShopCategory_DeleteShopCategory]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_shopCategory]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of ShopCategory from the ShopCategory 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_ShopCategory_DeleteShopCategoryList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_shopCategory]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of ShopCategory table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryById]
@Id Int
AS
SELECT
	id, 
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the ShopCategory database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_ShopCategory_GetAllShopCategory]
AS
SELECT
	id, 
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetAllShopCategoryPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shopCategory]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetAllShopCategoryPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shopCategory]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the ShopCategory database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_ShopCategory_UpdateShopCategory]
@Id int,
	@ShopId Int,
	@CategoryId Int,
	@ItemCount Int,
	@CreatedOn DateTime,
	@LastUpdate DateTime,
	@TotalView Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_shopCategory]
SET

	shop_id = @ShopId,
	category_id = @CategoryId,
	item_count = @ItemCount,
	created_on = @CreatedOn,
	last_update = @LastUpdate,
	total_view = @TotalView
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByShopId]
@ShopId Int
AS
SELECT 	id,
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE shop_id=@ShopId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByShopIdPaged]
(
@ShopId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shopCategory]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE shop_id = ' +  CONVERT(nvarchar(10),@ShopId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByShopIdPagedCount]
@ShopId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shopCategory]
	WHERE shop_id = @ShopId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByCategoryId]
@CategoryId Int
AS
SELECT 	id,
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE category_id=@CategoryId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByCategoryIdPaged]
(
@CategoryId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_shopCategory]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	shop_id,
	category_id,
	item_count,
	created_on,
	last_update,
	total_view

FROM [dbo].[tbl_shopCategory]
WHERE category_id = ' +  CONVERT(nvarchar(10),@CategoryId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_ShopCategory_GetShopCategoryByCategoryIdPagedCount]
@CategoryId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_shopCategory]
	WHERE category_id = @CategoryId


-- Returning the total number of records in the given query
Return @ReturnVal
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the Subscribe database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_Subscribe_CreateNewSubscribe]
	@Email NVarChar(64)

AS

INSERT INTO [dbo].[tbl_subscribe]
(
	email

)
VALUES
(
	@Email

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the Subscribe database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Subscribe_DeleteSubscribe]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_subscribe]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of Subscribe from the Subscribe 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Subscribe_DeleteSubscribeList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_subscribe]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of Subscribe table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Subscribe_GetSubscribeById]
@Id Int
AS
SELECT
	id, 
	email

FROM [dbo].[tbl_subscribe]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Subscribe database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Subscribe_GetAllSubscribe]
AS
SELECT
	id, 
	email

FROM [dbo].[tbl_subscribe]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Subscribe_GetAllSubscribePaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_subscribe]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	email

FROM [dbo].[tbl_subscribe]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_Subscribe_GetAllSubscribePagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_subscribe]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the Subscribe database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_Subscribe_UpdateSubscribe]
@Id int,
	@Email NVarChar(64)

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_subscribe]
SET

	email = @Email
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure inserts a new record in the User database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_User_CreateNewUser]
	@UserName NVarChar(32),
	@UserEmail NVarChar(64),
	@UserPassword NVarChar(32),
	@IsActive Bit,
	@IsVerified Bit,
	@ConfirmationCode NVarChar(64),
	@LastVisit DateTime,
	@MembershipId Int

AS

INSERT INTO [dbo].[tbl_user]
(
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

)
VALUES
(
	@UserName,
	@UserEmail,
	@UserPassword,
	@IsActive,
	@IsVerified,
	@ConfirmationCode,
	@LastVisit,
	@MembershipId

)

DECLARE @ReturnVal Int
SET @ReturnVal=@@IDENTITY
Return @ReturnVal
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a record from the User database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_User_DeleteUser]
@Id Int

AS
DECLARE @ReturnVal int
BEGIN TRAN

DELETE [dbo].[tbl_user]
WHERE id=@Id

SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure deletes a set of User from the User 
* database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_User_DeleteUserList]
@IdList NVarChar(256)
AS
BEGIN TRAN

DELETE [dbo].[tbl_user]
WHERE id in
(
	@IdList 
)
DECLARE @ReturnVal Int	
SET @ReturnVal	=	@@ROWCOUNT
COMMIT TRAN
-- returning the number of affected rows
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of User table based on it's primary key

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_User_GetUserById]
@Id Int
AS
SELECT
	id, 
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

FROM [dbo].[tbl_user]
WHERE id=@Id
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the User database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_User_GetAllUser]
AS
SELECT
	id, 
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

FROM [dbo].[tbl_user]
go



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_User_GetAllUserPaged]
(
@orderby varchar(50),
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_user]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

FROM [dbo].[tbl_user]
WHERE id >= ' + convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_User_GetAllUserPagedCount]
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_user]

-- Returning the total number of records in the given query
Return @ReturnVal
go




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure updates a record from the User database table.

* Stored procedure last modified on:
* Modification notes:
******************************************************************************/
CREATE PROCEDURE [dbo].[spr_User_UpdateUser]
@Id int,
	@UserName NVarChar(32),
	@UserEmail NVarChar(64),
	@UserPassword NVarChar(32),
	@IsActive Bit,
	@IsVerified Bit,
	@ConfirmationCode NVarChar(64),
	@LastVisit DateTime,
	@MembershipId Int

AS
DECLARE @ReturnVal Int
BEGIN TRAN

UPDATE [dbo].[tbl_user]
SET

	user_name = @UserName,
	user_email = @UserEmail,
	user_password = @UserPassword,
	is_active = @IsActive,
	is_verified = @IsVerified,
	confirmation_code = @ConfirmationCode,
	last_visit = @LastVisit,
	membership_id = @MembershipId
WHERE id=@Id


SET @ReturnVal=@@ROWCOUNT

COMMIT TRAN
Return @ReturnVal
go





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****************************************************************************
* Functional Comment:
* This stored procedure retunrns a record of employee table based on it's foreign key
* Stored procedure last modified on:
* Modification notes:
******************************************************************************/

CREATE PROCEDURE [dbo].[spr_User_GetUserByMembershipId]
@MembershipId Int
AS
SELECT 	id,
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

FROM [dbo].[tbl_user]
WHERE membership_id=@MembershipId

go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[spr_User_GetUserByMembershipIdPaged]
(
@MembershipId Int, @orderby varchar(50),@startRowIndex int,@maximumRows int
)
AS

DECLARE @first_id int, @sql nvarchar(4000)

SET @startRowIndex = @startRowIndex + 1
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex

SELECT @first_id = id FROM [dbo].[tbl_user]
ORDER BY id

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

set @sql = '
SELECT 
	id, 
	user_name,
	user_email,
	user_password,
	is_active,
	is_verified,
	confirmation_code,
	last_visit,
	membership_id

FROM [dbo].[tbl_user]
WHERE membership_id = ' +  CONVERT(nvarchar(10),@MembershipId) + ' AND id >= ' + 
convert(nvarchar(12),@first_id) +' ORDER BY ' + convert(nvarchar(50),@orderby)

exec sp_executesql @sql

SET ROWCOUNT 0
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[spr_User_GetUserByMembershipIdPagedCount]
@MembershipId Int
As

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @ReturnVal Int

	select @ReturnVal = count(*) from [dbo].[tbl_user]
	WHERE membership_id = @MembershipId


-- Returning the total number of records in the given query
Return @ReturnVal
go



